package com.monkeyk.sos.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.monkeyk.sos.domain.dto.CooperDetailsDto;
import com.monkeyk.sos.domain.model.ApplicationDetails;
import com.monkeyk.sos.domain.model.CooperDetails;
import com.monkeyk.sos.domain.model.ResourceDetail;
import com.monkeyk.sos.domain.user.Privilege;
import com.monkeyk.sos.domain.user.User;
import com.monkeyk.sos.service.CooperService;
import com.monkeyk.sos.util.Pager;
import com.monkeyk.sos.util.PaginationHelper;
import com.monkeyk.sos.util.WebUtils;

@Service("cooperService")
public class CooperServiceImpl implements CooperService {

	@Autowired
	private JdbcTemplate	jdbcTemplate;
	
	/*
     * 根据cooperName获得合作者基本账户信息
     * return:CooperDetails
     */
	@Override
	public CooperDetails findCooperDetailByCooperName(String cooperName) {
		String sqlApp = " select * from cooperator where  cooper_name = ?";
		java.util.List<CooperDetails> cooperDetailsList = this.jdbcTemplate.query(sqlApp, new Object[] { cooperName },
				new RowMapper<CooperDetails>() {
					@Override
					public CooperDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
						CooperDetails cooperDetails = new CooperDetails();
						cooperDetails.setCooperId(rs.getLong("id"));
						cooperDetails.setCooperName(rs.getString("cooper_name"));
						cooperDetails.setEmail(rs.getString("email"));
						cooperDetails.setPhone(rs.getString("phone"));
						cooperDetails.setProvince(rs.getString("province"));
						cooperDetails.setCity(rs.getString("city"));
						cooperDetails.setAddress(rs.getString("address"));
						cooperDetails.setPostcode(rs.getString("postcode"));
						cooperDetails.setSite(rs.getString("site"));
						cooperDetails.setContactPerson(rs.getString("contact_person"));
						cooperDetails.setContactPhone(rs.getString("contact_phone"));
						cooperDetails.setStatus(rs.getInt("status"));
						return cooperDetails;
					}
				});
		CooperDetails cooperDetails = null;
		if (!cooperDetailsList.isEmpty()) {
			cooperDetails = cooperDetailsList.get(0);
		}
		return cooperDetails;
	}
	
	
	
	/*
     * 根据cooperName获得合作者权限信息
     * return:User
     */
	@Override
	public User findUserByCooperName(String cooperName) {
		String sqlApp = " select * from cooperator where  cooper_name = ? and status != 2";
		java.util.List<CooperDetails> cooperDetailsList = this.jdbcTemplate.query(sqlApp, new Object[] { cooperName },
				new RowMapper<CooperDetails>() {
					@Override
					public CooperDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
						CooperDetails cooperDetails = new CooperDetails();
						cooperDetails.setCooperId(rs.getLong("id"));
						cooperDetails.setCooperName(rs.getString("cooper_name"));
						cooperDetails.setEmail(rs.getString("email"));
						cooperDetails.setPhone(rs.getString("phone"));
						cooperDetails.setPassword(rs.getString("password"));
						cooperDetails.setDefaultUser(rs.getInt("default_user"));
						cooperDetails.setStatus(rs.getInt("status"));
						return cooperDetails;
					}
				});
		User user = null;
		if (!cooperDetailsList.isEmpty()) {
			user = cooperDetailsList.get(0).createUser();
			user.privileges().addAll(findCooperPrivilege(cooperDetailsList.get(0).getCooperId().intValue()));
		}
		return user;
	}
    /*
     * 查询权限信息
     */
    private Collection<Privilege> findCooperPrivilege(int cooperId) {
		final String sql = " select privilege from cooper_privilege where cooper_id = ? ";
		final List<String> strings = this.jdbcTemplate.queryForList(sql, new Object[] { cooperId }, String.class);
		List<Privilege> privileges = new ArrayList<Privilege>(strings.size());
		for (String string : strings) {
			privileges.add(Privilege.valueOf(string));
		}
		return privileges;
	}
    
    
    /**
	 * 根据cooperName获得合作者应用信息
	 */
	@Override
	public CooperDetailsDto getCooperDetailsDto(String cooperName) {
		CooperDetailsDto CooperDetailsDto = new CooperDetailsDto(findCooperDetailByCooperName(cooperName));
		CooperDetailsDto.setApplicationDetailsList(findCooperDetailsDto(cooperName));
		return CooperDetailsDto;
	}

	/**
	 * 根据cooperName获得应用详情
	 */
	private List<ApplicationDetails> findCooperDetailsDto(String cooperName) {
		String sqlApp ="SELECT c.id,c.app_name,c.web_name,c.status FROM  cooperator a LEFT JOIN cooper_app b ON a.id = b.cooper_id LEFT JOIN application_details c ON c.id = b.app_id  where  a.cooper_name = ?";
		java.util.List<ApplicationDetails> applicationDetailsList = this.jdbcTemplate.query(sqlApp,new Object[] { cooperName }, new RowMapper<ApplicationDetails>() {
					@Override
					public ApplicationDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
						ApplicationDetails applicationDetails = new ApplicationDetails();
						applicationDetails.setAppId(rs.getLong("id"));
						applicationDetails.setAppName(rs.getString("app_name"));
						applicationDetails.setWebName(rs.getString("web_name"));
						applicationDetails.setStatus(rs.getInt("status"));
						return applicationDetails;
					}
				});
		return applicationDetailsList;
	}
	
	@Transactional
	@Override
	public void saveCooper(CooperDetails cooper) {
		Long cooperId = saveCooperDetails(cooper);
		saveCooperPrivilege(cooperId,"COOPER");
	}
	
	
	/*
	 * 保存合作者基本信息
	 */
	public Long saveCooperDetails(final CooperDetails cooperDetails) {
		final String sql = " insert into cooperator(cooper_name,password,email,phone,province,city,address,postcode,site,contact_person,contact_phone) values (?,?,?,?,?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, cooperDetails.getCooperName());
				ps.setString(2, cooperDetails.getPassword());
				ps.setString(3, cooperDetails.getEmail());
				ps.setString(4, cooperDetails.getPhone());
				ps.setString(5, cooperDetails.getProvince());
				ps.setString(6, cooperDetails.getCity());
				ps.setString(7, cooperDetails.getAddress());
				ps.setString(8, cooperDetails.getPostcode());
				ps.setString(9, cooperDetails.getSite());
				ps.setString(10, cooperDetails.getContactPerson());
				ps.setString(11, cooperDetails.getContactPhone());
				return ps;
			}
		}, keyHolder);
		Long generatedId = keyHolder.getKey().longValue();
		return generatedId;
	}
	
	
	/*
	 * 合作者-权限表 cooper_privilege
	 */
	private void saveCooperPrivilege(final Long cooperId, final String privilege) {
		final String sql = " insert into cooper_privilege(cooper_id,privilege) values (?,?)";
		this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setLong(1, cooperId);
				ps.setString(2, privilege);
			}
		});
	}


	/*
	 * 更新合作者基本信息
	 */
	@Transactional
	@Override
	public void updateCooperDetail(final CooperDetails cooperDetails) {
		if(cooperDetails.getPassword()!="" && cooperDetails.getPassword()!=null){
			this.jdbcTemplate.update("update cooperator set password = '"+WebUtils.md5(cooperDetails.getPassword())+"' where id ="+cooperDetails.getCooperId());
		}else{
			String sqlApp = " update cooperator set cooper_name = ?,email = ? ,phone = ?,province=?,city=?,address = ?  where id = ?";
			this.jdbcTemplate.update(sqlApp, new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setObject(1, cooperDetails.getCooperName());
					ps.setObject(2, cooperDetails.getEmail());
					ps.setObject(3, cooperDetails.getPhone());
					ps.setObject(4, cooperDetails.getProvince());
					ps.setObject(5, cooperDetails.getCity());
					ps.setObject(6, cooperDetails.getAddress());
					ps.setObject(7, cooperDetails.getCooperId());
				}
			});
		}
	}



	@Override
	public List<ResourceDetail> getResource() {
		String sqlApp = " SELECT * FROM resource ";
		java.util.List<ResourceDetail> resourceList = this.jdbcTemplate.query(sqlApp,
				new RowMapper<ResourceDetail>() {
					@Override
					public ResourceDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
						ResourceDetail resource = new ResourceDetail();
						resource.setIds(rs.getString("ids"));
						resource.setName(rs.getString("name"));
						return resource;
					}
				});
		return resourceList;
	}

	/*
	 * 更新合作者状态
	 */
	@Override
	public void updateStatus(int status,String cooperId) {
		String sql="update cooperator set status = "+status+"  where id = "+cooperId;
		this.jdbcTemplate.update(sql);
	}


	/*
	 * 查询用户名是否存在
	 */
	@Override
	public boolean checkCooper(String cooperName) {
		final String sql = " select count(0) from cooperator where  cooper_name = '"+cooperName+"'";
		Integer coun = jdbcTemplate.queryForObject(sql,Integer.class);
		if(coun == 0){
			return true;
		}else{
			return false;
		}
	}


	/*
	 * 查询手机是否存在
	 */
	@Override
	public boolean checkCooperPhone(String phone) {
		final String sql = " select count(0) from cooperator where  phone = '"+phone+"'";
		Integer coun = jdbcTemplate.queryForObject(sql,Integer.class);
		if(coun == 0){
			return true;
		}else{
			return false;
		}
	}



	/**
	 * 得到选择的合作者详情
	 * @param cooperDetails
	 * @param pagingInfo
	 * @return
	 */
	public List<CooperDetails> getSelectedCooperDetailsList(CooperDetails cooperDetails, Pager pagingInfo) {
	    String sql = "SELECT * FROM  cooperator  LIMIT "
		         +(pagingInfo.getPage()-1)*pagingInfo.getPageSize()
		         +","
		         +pagingInfo.getPageSize();
	    java.util.List<CooperDetails> cooperDetailsList = this.jdbcTemplate.query(sql,
			new RowMapper<CooperDetails>() {
				@Override
				public CooperDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
				    CooperDetails cooperDetails = new CooperDetails();
					cooperDetails.setCooperId(rs.getLong("id"));
					cooperDetails.setCooperName(rs.getString("cooper_name"));
					cooperDetails.setPassword(rs.getString("password"));
					cooperDetails.setEmail(rs.getString("email"));
					cooperDetails.setPhone(rs.getString("phone"));
					cooperDetails.setAddress(rs.getString("address"));
					cooperDetails.setProvince(rs.getString("province"));
					cooperDetails.setCity(rs.getString("city"));
					cooperDetails.setStatus(rs.getInt("status"));
					return cooperDetails;
				}
			});
	    
	    
	return cooperDetailsList;
	}
	
	/**
	 * 得到所有合作者数据总数
	 * @return int
	 */
	public int  getAllCooperDetailsTotals() {
	    String sqlCountRows = "select count(*) from cooperator";
            return PaginationHelper.findTotalCount(this.jdbcTemplate, sqlCountRows, null);
	}
}
